﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;
using static System.Net.Mime.MediaTypeNames;
using System.Drawing.Printing;
using BTS.Models;
using System.IO;

namespace BTS.Helpers.DataBaseHelper
{
    public class SqliteDataBase : IDataBase
    {

        public SqliteDataBase(string connectionString, string psw = "")
        {
            _connectionString = $"Data Source = {connectionString}";
        }
        private readonly string _connectionString;

        public bool Insert(string Table, string query)
        {
            try
            {
                string sql = $"INSERT INTO {Table}{query}";
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        if (command.ExecuteNonQuery() >= 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool Delete(string Table, string query)
        {
            try
            {
                string sql = $"DELETE FROM {Table} WHERE {query}";
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        if (command.ExecuteNonQuery() >= 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Clear(string Table)
        {
            try
            {
                string sql = $"DELETE FROM {Table}";
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        if (command.ExecuteNonQuery() >= 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        public bool Update(string Table, string query)
        {
            try
            {

                string sql = $"UPDATE {Table} SET {query}";

                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        if (command.ExecuteNonQuery() >= 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception)
            {
                return false;
            }
        }

        public DataTable QueryAll(string Table)
        {
            DataTable dt = new DataTable();
            try
            {

                string sql = $"SELECT *FROM {Table}";
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.ExecuteNonQuery();
                        using (SQLiteDataAdapter da = new SQLiteDataAdapter(command))
                        {
                            da.Fill(dt); // 使用DataAdapter填充DataTable
                        }
                        return dt;
                    }
                }
            }
            catch (Exception)
            {
                return dt;
            }
        }

        public DataTable QuerySomething(string query)
        {
            DataTable dt = new DataTable();
            try
            {
                string sql = $"SELECT {query}";
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        command.ExecuteNonQuery();
                        using (SQLiteDataAdapter da = new SQLiteDataAdapter(command))
                        {
                            da.Fill(dt); // 使用DataAdapter填充DataTable
                        }
                        return dt;
                    }
                }
            }
            catch (Exception)
            {
                return dt;
            }
        }

        public int GetTotalRows(string query)
        {
            int totalCount = 0;
            try
            {
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SQLiteCommand(query, connection))
                    {
                        totalCount = Convert.ToInt32(command.ExecuteScalar());
                        return totalCount;
                    }
                }
            }
            catch (Exception)
            {
                return totalCount;
            }
        }

        public bool CreateTable(string Table, string CreateCmd)
        {
            //SqliteDataBase sqliteDataBase = new SqliteDataBase();
            //sqliteDataBase.Table = "DB";
            //sqliteDataBase.Connect(Application.StartupPath + @"\NoteBook.db", "");
            //sqliteDataBase.CreateTable(@"( ""ID"" INTEGER NOT NULL,""UserName""  TEXT NOT NULL UNIQUE, ""UserPSW"" TEXT NOT NULL, PRIMARY KEY(""ID"" AUTOINCREMENT));)");
            try
            {
                string sql = $"CREATE TABLE IF NOT EXISTS \"{Table}\" ({CreateCmd});";
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        if (command.ExecuteNonQuery() >= 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public string[] GetAllColumnsName(string Table)
        {
            string[] colNames = null;
            try
            {

                DataTable dataTable = new DataTable();

                string sql = $"SELECT TOP 1 * FROM {Table}";
                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SQLiteCommand(sql, connection))
                    {
                        using (SQLiteDataReader reader = command.ExecuteReader())
                        {
                            dataTable.Load(reader);
                        }

                        colNames = new string[dataTable.Columns.Count];
                        for (int i = 0; i < dataTable.Columns.Count; i++)
                        {
                            colNames[i] = dataTable.Columns[i].ColumnName;
                        }

                        return colNames;
                    }
                }
            }
            catch (Exception)
            {
                return colNames;
            }
        }

        public bool AddColumnName(string Table, DBColumnModel[] Cols)
        {
            try
            {

                string sql = "";
                bool blErrFlag = true;
                for (int i = 0; i < Cols.Length; i++)
                {
                    sql = $"ALTER TABLE {Table} ADD {Cols[i].ColName} {Cols[i].Description}";

                    using (var connection = new SQLiteConnection(_connectionString))
                    {
                        connection.Open();
                        using (var command = new SQLiteCommand(sql, connection))
                        {
                            if (command.ExecuteNonQuery() >= 0)
                                continue;
                            else
                            {
                                blErrFlag = false;
                                break;
                            }
                        }
                    }
                }
                return blErrFlag;

            }
            catch (Exception)
            {
                return false;
            }

        }

        public bool Compress()
        {
            try
            {

                using (var connection = new SQLiteConnection(_connectionString))
                {
                    connection.Open();
                    using (var command = new SQLiteCommand("VACUUM", connection))
                    {
                        if (command.ExecuteNonQuery() >= 0)
                            return true;
                        else
                            return false;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        public bool CreateNewDBFile(string dbPath, string PSW="")
        {
            try
            {

                if (File.Exists(dbPath))//数据库文件已存在
                {
                    return true;
                }

                if (!Directory.Exists(Path.GetDirectoryName(dbPath)))
                {
                    Directory.CreateDirectory(Path.GetDirectoryName(dbPath));
                }
                SQLiteConnection.CreateFile(dbPath);

                return true;
            }
            catch (Exception)
            {
                return false;
            }
        }
    }
}
